# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

from init_mysql import session,session2
from models import VulNsfocus, VulNvd, VulCnvd, VulSeeBug, VulSecurityFocus, VulExploit, VulSecurityTracker, \
    VulPacketStormSecurity, VulMitre, VulCnnvd, VulMicro, ShowVuls, VulType, Schedule,  VulLevel
from dingdian.items import  NvdItem, NsfocusItem, CnvdItem, SeeBugItem, SecurityFocusItem, ExploitItem, \
    SecurityTrackerItem, PacketStormSecurityItem, MitreItem, CnnvdItem, MicroItem
import pymysql
import os
class DingdianPipeline(object):
    def process_item(self, item, spider):

        url = item['url']
        title = item['title']
        chinese = item['chinese']
        publish_time = item['publishTime']
        total = item['total']
        current = item['current']
        try:
            level = item['level']
        except Exception:
            level = None

        try:
            poc = item['poc']
        except Exception:
            poc = "暂无"

        schedule=session.query(Schedule).filter(Schedule.pid == os.getpid()).all()

        if schedule != [] :

                schedule[0].current=current
                schedule[0].total = total
                try:
                    session.commit()
                except Exception:
                    session.rollback()
        else:
            new_schedule = Schedule(spider_name=spider.name, current=current,
                                    total=total,pid=os.getpid())
            session.add(new_schedule)
            try:
                session.commit()
            except Exception:
                session.rollback()
        try:
            cveId = item['cveID']
        except:
            cveId = None
        try:
            type = pymysql.escape_string(item['type'])
        except:
            type = None

        try:
            update_time = item['updateTime']
        except:
            update_time = None
        try:
            item['effectSys'] = item['effectSys'].strip().replace('\t', '').replace('\r\n', ' ').replace('\n', ' ')
            effect_sys = pymysql.escape_string(item['effectSys'])
        except:
            effect_sys =  None
        try:
            item['message'] = item['message'].strip().strip().replace('\t', '').replace('\r\n', ' ').replace('\n', ' ')

            message = pymysql.escape_string(item['message'])
            if message=="登录后查看":
                message = None
        except:
            message = None
        try:
            item['suggestion'] = item['suggestion'].strip().strip().replace('\t', '').replace('\r\n', ' ').replace('\n', ' ')

            suggestion = pymysql.escape_string(item['suggestion'])
        except:
            suggestion = None
        try:
            item['vendorPatch'] = item['vendorPatch'].strip().strip().replace('\t', '').replace('\r\n', ' ').replace('\n', ' ')

            vendor_patch = pymysql.escape_string(item['vendorPatch'])
        except:
            vendor_patch = None

        #print item
        if cveId == None or cveId == "" or cveId == " ":
            pass
        else:

            if type ==None or type =="":
                type = "其他"
            if level ==None or level =="":
                level = "暂无"
            elif level =="中" :
                level = "中危"
            elif level =="高" :
                level = "高危"
            elif level =="低" :
                level = "低危"


            if message ==None or message =="" or message ==" ":
                message = "暂无"

            if poc ==None or poc =="" or poc ==" ":
                poc = "暂无"

            if effect_sys ==None or effect_sys =="" or effect_sys ==" ":
                effect_sys = "暂无"
            if vendor_patch ==None or vendor_patch =="" or vendor_patch ==" ":
                vendor_patch = "暂无"
            if suggestion ==None or suggestion =="" or suggestion ==" ":
                suggestion = "暂无"
            if update_time ==None or update_time =="" or update_time ==" ":
                update_time = "暂无"

            vulLevel = session2.query(VulLevel).filter(VulLevel.level == level).all()
            #print vulType
            if  vulLevel!=[]:
                vulLevel =  vulLevel[0]
            else:
                vulLevel = VulLevel(level=level)
                session2.add(vulLevel)
                try:
                    session2.commit()
                except Exception as e:
                    #print e
                    session2.rollback()
                vulLevel = session2.query(VulLevel).filter(VulLevel.level == level).first()

            #print poc
            #print type
            vulType = session2.query(VulType).filter(VulType.type == type).all()
            #print vulType
            if vulType!=[]:
                vulType = vulType[0]
            else:
                vulType = VulType(type=type)
                session2.add(vulType)
                try:
                    session2.commit()
                except Exception as e:
                    print e
                    session2.rollback()
                vulType = session2.query(VulType).filter(VulType.type == type).first()
            #print vulType






            tempVul = session2.query(ShowVuls).filter(ShowVuls.cve_id == cveId).all()

            if tempVul!= []:
                tempVul = tempVul[0]
                urls = tempVul.url.split('\n')
                if url not in urls or (url in urls and tempVul.update_time<update_time):
                    if url not in urls:
                        tempVul.url = tempVul.url+'\n'+url

                    if vendor_patch !=None and vendor_patch!=""and vendor_patch!="暂无":
                        if tempVul.vendor_patch !="暂无":
                            tempVul.vendor_patch =tempVul.vendor_patch+'\n'+vendor_patch
                        else:
                            tempVul.vendor_patch = vendor_patch

                    if tempVul.publish_time > publish_time and publish_time>"1980":
                        tempVul.publish_time = publish_time

                    if update_time !="暂无":
                        if tempVul.update_time != None:
                            if tempVul.update_time<update_time:
                                tempVul.update_time = update_time
                        else:
                            tempVul.update_time = update_time

                    if title!=None and title!="":
                        if tempVul.title != None and tempVul.title!="":
                            if tempVul.chinese ==False:
                                if chinese == True:
                                    tempVul.title = title
                                elif len(title)>len(tempVul.title):
                                    tempVul.title=title
                            else:
                                if chinese==True:
                                    if len(title)>len(tempVul.title):
                                        tempVul.title = title
                        else:
                            tempVul.title=title

                    if effect_sys!="暂无":
                        if tempVul.effect_sys != "暂无":
                            if tempVul.chinese ==False:
                                if chinese == True:
                                    tempVul.effect_sys = effect_sys
                                elif len(effect_sys)>len(tempVul.effect_sys):
                                    tempVul.effect_sys=effect_sys
                            else:
                                if chinese==True:
                                    if len(effect_sys)>len(tempVul.effect_sys):
                                        tempVul.effect_sys = effect_sys
                        else:
                            tempVul.effect_sys=effect_sys

                    if message!="暂无":
                        if tempVul.message != "暂无":
                            if tempVul.chinese ==False:
                                if chinese == True:
                                    tempVul.message = message
                                elif len(message)>len(tempVul.message):
                                    tempVul.message=message
                            else:
                                if chinese==True:
                                    if len(message)>len(tempVul.message):
                                        tempVul.message = message
                        else:
                            tempVul.message=message

                    if vulLevel.level != "暂无" and tempVul.levels.level =='暂无':
                        tempVul.levelid=vulLevel.id

                    if vulType.type!="其他":
                        if tempVul.types.type != "其他":
                            if tempVul.chinese ==False:
                                if chinese == True:
                                    tempId = tempVul.typeid
                                    tempVul.typeid = vulType.id
                                    tempType = session2.query(VulType).filter(VulType.id == tempId).first()
                                    #print "!!!!!!!!!!!!!!1you chong fu!!!!!!!!!! dan cheng gong "
                                    for vul in tempType.vuls:
                                        vul.typeid = vulType.id
                                        #print vul.cve_id,' ',vul.id
                                    # session2.commit()
                                    # session2.delete(tempType)

                                    try:
                                        session2.commit()
                                    except Exception:
                                        session2.rollback()
                                elif len(vulType.type)>len(tempVul.types.type):
                                    #print "***********8you chong fu********* dan cheng gong "
                                    tempId = tempVul.typeid
                                    tempVul.typeid = vulType.id
                                    tempType = session2.query(VulType).filter(VulType.id == tempId).first()
                                    for vul in tempType.vuls:
                                        vul.typeid = vulType.id
                                        #print vul.cve_id
                                    # session2.commit()
                                    # session2.delete(tempType)

                                    try:
                                        session2.commit()
                                    except Exception:
                                        session2.rollback()
                            else:
                                if chinese==True:
                                    if len(vulType.type)>len(tempVul.types.type):
                                        tempId = tempVul.typeid
                                        tempVul.typeid = vulType.id
                                        tempType = session2.query(VulType).filter(VulType.id == tempId).first()
                                        for vul in tempType.vuls:
                                            vul.typeid = vulType.id
                                            # print vul.cve_id
                                        # session2.commit()
                                        # session2.delete(tempType)

                                        try:
                                            session2.commit()
                                        except Exception:
                                            session2.rollback()
                        else:
                            tempVul.typeid = vulType.id


                    if poc!="暂无":
                        tempVul.poc=poc

                    if chinese == True:
                        tempVul.chinese = True

                    try:
                        session2.commit()
                    except Exception:
                        session2.rollback()
            else:
                new_vul = ShowVuls(  url=url,
                                 title=title,
                                 cve_id=cveId,
                                 levelid=vulLevel.id,
                                 typeid = vulType.id,
                                 publish_time=publish_time,
                                 update_time=update_time,
                                 effect_sys=effect_sys,
                                 poc=poc,
                                 message=message,
                                 vendor_patch=vendor_patch,
                                 chinese = chinese)
                session2.add(new_vul)
                try:
                    session2.commit()
                except Exception:
                    session2.rollback()

        # if isinstance(item,NvdItem):
        #     new_vul = VulNvd(cve_id=item['cveID'], publish_time=item['publishTime'], update_time=item['updateTime']
        #     ,url=item['url'], title=item['title'], type=item['type'],vendor_patch=item['vendorPatch'],
        #                      message=item['message'])
        #
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        #
        # elif isinstance(item,NsfocusItem):
        #         new_vul = VulNsfocus(   url=item['url'],title = item['title'],
        #                                 cve_id = pymysql.escape_string(item['cveID']),
        #                                 publish_time = item['publishTime'],
        #                                 update_time = item['updateTime'],
        #                                 effect_sys = pymysql.escape_string(item['effectSys']),
        #                                 message = pymysql.escape_string(item['message']),
        #                                 suggestion = pymysql.escape_string(item['suggestion']),
        #                                 vendor_patch = pymysql.escape_string(item['vendorPatch']))
        #         session.add(new_vul)
        #         try:
        #             session.commit()
        #         except Exception:
        #             session.rollback()
        #         return item
        #
        # elif isinstance(item, CnvdItem):
        #     if (session.query(VulCnvd).filter(VulCnvd.cve_id ==item['cveID']).all()) != []:
        #         print "已经解析过该网页"
        #         return None
        #     new_vul = VulCnvd(url=item['url'], title=item['title'],
        #                       cve_id=item['cveID'],
        #                       publish_time=item['publishTime'],
        #                       update_time=item['updateTime'],
        #                       effect_sys=pymysql.escape_string(item['effectSys']),
        #                       message=pymysql.escape_string(item['message']),
        #                       suggestion=pymysql.escape_string(item['suggestion']),
        #                       vendor_patch=pymysql.escape_string(item['vendorPatch']))
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        #
        # elif isinstance(item, SeeBugItem):
        #     if (session.query(VulSeeBug).filter(VulSeeBug.url ==item['url']).all()) != []:
        #         print "已经解析过该网页"
        #         return None
        #     new_vul = VulSeeBug(url=item['url'], title=item['title'],
        #                       cve_id=item['cveID'],
        #                       publish_time=item['publishTime'],
        #                       update_time=item['updateTime'],
        #                       effect_sys=pymysql.escape_string(item['effectSys']),
        #                       message=pymysql.escape_string(item['message']),
        #                       suggestion=item['suggestion'],
        #                       source=item['source'],
        #                       vendor_patch=item['vendorPatch'])
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        #
        # elif isinstance(item, SecurityFocusItem):
        #
        #     new_vul = VulSecurityFocus(cve_id=item['cveID'],
        #                                publish_time=item['publishTime'],
        #                                update_time=item['updateTime'],
        #                                url=item['url'],
        #                                title=item['title'],
        #                                type=item['type'],
        #                                vendor_patch=item['vendorPatch'],
        #                                suggestion=item['suggestion'],
        #                                message=pymysql.escape_string(item['message']),
        #                                effect_sys=pymysql.escape_string(item['effectSys']),
        #
        #                                )
        #
        #
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        #
        #
        # elif isinstance(item, ExploitItem):
        #
        #     new_vul = VulExploit(cve_id=item['cveID'],
        #                                publish_time=item['publishTime'],
        #                                url=item['url'],
        #                                title=item['title'],
        #                                type=item['type'],
        #                                message=pymysql.escape_string(item['message']),
        #                                effect_sys=pymysql.escape_string(item['effectSys']),
        #                                edb_id=item['EDB_ID']
        #                                )
        #
        #
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        # elif isinstance(item, SecurityTrackerItem):
        #
        #     new_vul = VulSecurityTracker(cve_id=item['cveID'],
        #                                publish_time=item['publishTime'],
        #                                url=item['url'],
        #                                title=item['title'],
        #                                type=item['type'],
        #                                message=pymysql.escape_string(item['message']),
        #                                effect_sys=item['effectSys'],
        #                                tracker_id=item['trackerId'],
        #                                vendor_patch=item['vendorPatch'],
        #
        #                                )
        #
        #
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        #
        # elif isinstance(item, PacketStormSecurityItem):
        #     if (session.query(VulPacketStormSecurity).filter(VulPacketStormSecurity.url ==item['url']).all()) != []:
        #         print "已经解析过该网页"
        #         return None
        #
        #     new_vul = VulPacketStormSecurity(cve_id=item['cveID'],
        #                                  publish_time=item['publishTime'],
        #                                  url=item['url'],
        #                                  title=item['title'],
        #                                  message=pymysql.escape_string(item['message']),
        #                                  effect_sys=item['effectSys'],
        #                                  detail=pymysql.escape_string(item['detail'])
        #
        #                                  )
        #     try:
        #         session.add(new_vul)
        #         try:
        #             session.commit()
        #         except Exception:
        #             session.rollback()
        #
        #         return item
        #     except:
        #         return None
        #
        # elif isinstance(item, MitreItem):
        #     if (session.query(VulMitre).filter(VulMitre.cve_id ==item['cveID']).all()) != []:
        #         print "已经解析过该漏洞"
        #         return None
        #     new_vul = VulMitre(cve_id=item['cveID'],
        #                                  publish_time=item['publishTime'],
        #                                  update_time=item['updateTime'],
        #                                  url=item['url'],
        #                                  title=item['title'],
        #                                  message=pymysql.escape_string(item['message']),
        #                                  vendor_patch=  pymysql.escape_string(item['vendorPatch'])
        #                                  )
        #
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        #
        # elif isinstance(item, CnnvdItem):
        #
        #     new_vul = VulCnnvd(cve_id=item['cveID'],
        #                        publish_time=item['publishTime'],
        #                        update_time=item['updateTime'],
        #                        url=item['url'],
        #                        title=item['title'],
        #                        type=item['type'],
        #                        vendor_patch=item['vendorPatch'],
        #                        message=pymysql.escape_string(item['message']),
        #
        #                        )
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
        #
        # elif isinstance(item, MicroItem):
        #
        #     new_vul = VulMicro(cve_id=item['cveID'],
        #                        publish_time=item['publishTime'],
        #                        update_time=item['updateTime'],
        #                        url=item['url'],
        #                        title=item['title'],
        #                        effect_sys=item['effectSys'],
        #                        message=pymysql.escape_string(item['message']),
        #                        ms_id=item['msID'],
        #
        #                        )
        #     session.add(new_vul)
        #     try:
        #         session.commit()
        #     except Exception:
        #         session.rollback()
        #     return item
